Microsoft Excel Graphing a straight line from your data
Updates for Office 2007 in blue. The general directions are still good.
Data Input
Input x data in a column (or
what will become x data)
Input y data in another
column (or what will become y data)
Data Manipulation
Hints
•Before entering a formula, use
an “=” sign.
•The button “fx” is will give a list of common functions and
formulas, this is useful for complex functions like
standard deviation and logarithms.
•Using the copy
function changes the cell designation.
If you do not wish to do that,
put a dollar sign ($) before the letter and number of the cell designation.
Graphing a Straight Line
From options bar, choose Insert
then Chart (Or
click on “Chart Wizard” Icon)
“Chart Wizard” Menu will
appear
In office 2007 it is just an icon labeled “Scatter” under the Insert
tab
Under “Standard Types,”
Choose (under “Chart type:” ) XY (Scatter)
Choose (under “Chart subtype:”) the icon without anything connecting the points
Click on Next>
Under “Data Range”, click in
box to right of “Data range”
Highlight both x and y data
columns*
(note: may already be done, if so a dashed line flashes
around data)
Be sure that under “series
in:” there is a dot in the circle before “Columns”
(if not, click in that circle)
Click Next>
Under “Titles”, click in box
for “Chart title”
Type in name/title of graph
(e.g.,
Graph 1. Spectrophotometric Calibration Curve for Fe)
Click in box for “Value
(X) Axis”
Type in title for your
x-axis, include units
(e.g.,
concentration (ppm))
Click in box for “Value
(Y) Axis”
Type in title for your
y-axis, include units
(e.g. absorbance)
Click on “Legend”—which is at
the top of the Chart Wizard window
Click on checkmark next to “Show
legend”, checkmark should disappear
Click on Next>
Under “Place Chart,” click on circle to let of
“As new sheet”, dot should appear in circle
Click on Finish
Your graph should appear on
its own page.
Modifying axes
Double click on the
y-axis. “Format Axis” window appears
Click on “Number” at top
Format Axis window
Under “Category” choose
“number”
Input number of decimal places
corresponding to the significant figures in your y-axis.
Click ok
Repeat for x-axis.
Adding trendline
From option bar, choose Chart
then Add Trendline
From “Add Trendline”
window, under “Type”, choose Linear
If you want the equation
of the line and correlation coefficient displayed on the graph:
Click on “Options” at top of Add Trendline
window
Click on box to left of “Display equation on chart”, a
checkmark appears (optional)
Click on box to left of “Display R-squared on chart”, a
checkmark appears (optional)
Click on ok
Save.
You may the modify graph to
make it prettier or go directly to “Print.”
Graphing a
Titration Curve
Follow the directions for
graphing a line, except
•from the xy
(scatter graph) choices, choose the graph with a smooth line between data
points
•do not add a trendline (THERE ISN”T ONE!)
Suggestions to Pretty it up
• Choose a point in you graph
where nothing occurs, click to get the “plot area” menu. Choose the color white. This is not only prettier (in my opinion) than
a gray background, but it saves printer ink.
•You might want to move the
equation of the line and R2 to a different location
•Under Chart Options, use the
“gridlines” tab so that either there are gridlines in both
directions (or none at all)
If you want to graph more
than one set of data
Or the x and y column are not adjacent with x on the
left
Under the Data Range Section
of Chart Wizard (or “Source Data” under Chart in the options bar)
Click on the tab for “series”
If the “Series Box” say “series
1,” the x and y data ranges are correct and
you will have more than one data set
Click in the Name box, type in a description of that data
set
If the “Series Box” say
“series 1,” the x and y data ranges are incorrect
If more than one data set, click in the Name box and type
a description of the data (else leave blank)
Highlight entire “X values” box, click on spreadsheet
icon next to box, highlight x values in the spreadsheet
Highlight entire “Y values” box, click on spreadsheet
icon next to box, highlight y values in the
spreadsheet
If there is nothing in the
series box, or you are adding a data set click on the “Add” button
If there is more than one data set, click in the
Data Analysis of a
Straight Line
If doing this for the first
time in Office 2003
From
the option bar choose Tools then Add-Ins
(You may have to click on the arrow
or wait for add-ins to appear in the menu.)
Click
on the box to the left of “Analysis Toolpack”
(If on a home computer, you may need
your original disks/CD to do this.)
Click
on ok
If doing this for
the first time in Office 2007
click on office
button in upper left corner of the screen
Choose Excel Options on the bottom rightish of the menu
Select “Add-Ins” from the menu bar
on the left
Click on the box next to “Analysis Toolpak”
Click “ok”
After “data analysis” has
been set up...
From
the option bar choose Tools then Data Analysis
scroll
down to “Regression” and click on the word to highlight it
Click
on ok
In the Regression window
Click
in the white space next to “Input Y Range”
With
mouse, highlight the y column of data
Click
in white space next to “Input X Range”
With
mouse, highlight the x column of data
Options:
If
the first item in your column is not data, but the data description, click on
“labels”
If
you want the y-intercept to be zero rather than calculated, click on “constant
is zero”
If
you don’t want the results on this on a different page, click in the circle to
the left of “output range”
then
click in the white space next to “output range” and use your mouse to highlight
a cell for the upper left-hand corner of where you statistics will come
out. Leave lots of space since it is 9
cells across and 18 cells down.
Interpreting your output...at
least the results relevant to Dr. Myers
(note: some of
the titles may be partially hidden because of the column size)
Under
Regression Statistics
•The
“R Square” value represents how linear your line is. The closer this value is to 1.0000, the more
it is like a line than a curve. Rarely
is any value less than 0.9 acceptable.
If your value is less than 0.9, consider a different type of “fit”
•The
“observations” value is the number of data points. You might find it useful to see if you
actually included all your data or if you have more data than you want to count
Under
the last section, where there are rows for “intercept” and “x variable”
•intercept
refers to the y-intercept or b in the formula y = mx+b
the value
for b is in the next column under the title “coefficient”
the error
in this value is in the column after that under the title “standard errors”
•x
variable refers to the slope or m in the formula y = mx+b
the value
for m is in the column titled “coefficient”
the error
in this value is in the column titled “standard errors”